// 云函数入口文件
const cloud = require('wx-server-sdk')

cloud.init()
const db = cloud.database()
//操作excel用的类库
const xlsx = require('node-xlsx');

// 云函数入口函数
exports.main = async(event, context) => {
  try {
    let res = await db.collection('profiles')
    .aggregate()
    .limit(1000)
    .end()
    console.log(res.list);
    let items = res.list;
    let obj = {

    };
    items.forEach(element => {
      let userInfo = element.userInfo;
      obj[element._openid] = Object.assign({}, {name:'',grade:'',className:''}, userInfo);
    });

    let res2 = await db.collection('historys')
    .aggregate()
    .limit(1000)
    .end()

    let items2 = res2.list;
    items2.forEach(element => {
      element.userInfo = obj[element._openid];
      console.log(element);
    });

    

    //1,定义excel表格名
    let dataCVS = '202005080800.xlsx'
    //2，定义存储数据的
    let alldata = [];
    let row = ['id','openid','答题时间','一级','二级','分数','姓名','年级','班级']; //表属性
    alldata.push(row);

    for (let key in items2) {
      console.log(key);
      console.log(items2[key]);
      let arr = [];
      arr.push(items2[key]._id);
      arr.push(items2[key]._openid);
      arr.push(items2[key].createTime);
      arr.push(items2[key].subject['examname']);
      arr.push(items2[key].subject['name']);
      arr.push(items2[key].rightNum);
      if(items2[key].userInfo){
        arr.push(items2[key].userInfo.name || '');
        arr.push(items2[key].userInfo.grade || '');
        arr.push(items2[key].userInfo.className || '');
      }

      

      alldata.push(arr)
    }
    //3，把数据保存到excel里
    var buffer = await xlsx.build([{
      name: "mySheetName",
      data: alldata
    }]);
    //4，把excel文件保存到云存储里
    return await cloud.uploadFile({
      cloudPath: dataCVS,
      fileContent: buffer, //excel二进制文件
    })

  } catch (e) {
    console.error(e)
    return e
  }
}